from MySQLdb import connect, OperationalError


class Connection():
    def __init__(self, host, user, passwd, db):
        self.host = host
        self.user = user
        self.passwd = passwd
        self.dbname = db
        self.connect()

    def connect(self):
        self.db = connect(host=self.host, user=self.user, passwd=self.passwd, db=self.dbname)
        #db = connect(host='localhost', user='root', passwd='root', db='magento')
        self.db.set_character_set('utf8')
        self.cursor = self.db.cursor()
        self.cursor.execute('SET NAMES utf8;')
        self.cursor.execute('SET CHARACTER SET utf8;')
        self.cursor.execute('SET character_set_connection=utf8;')

    def commit(self):
        try:
            self.db.commit()
        except (AttributeError, OperationalError):
            print
            self.connect()

    def sqlQuery(self, sql):
        try:
        #            print sql + "\n"
            self.cursor.execute(sql)
        except (AttributeError, OperationalError):
            if self.db:
                self.db.close()
            self.connect()
            self.cursor.execute(sql)
        finally:
            return self.cursor.fetchall()


# db = Connection('5.9.82.251', 'root', '11235813213455', 'magento')
# res = db.sqlQuery('''SELECT * FROM firsteav_attribute where attribute_code='kod_1c';''')
# sets = db.sqlQuery('''SELECT * FROM firsteav_attribute_set where entity_type_id=4;''')
# for set in sets:
#     group = db.sqlQuery('''SELECT * FROM firsteav_attribute_group where attribute_set_id=%s and
#                         attribute_group_name='General';''' % set[0])
#     db.sqlQuery('''INSERT INTO `firsteav_entity_attribute` (`entity_type_id`, `attribute_set_id`, `attribute_group_id`,
#      `attribute_id`, `sort_order`) VALUES ('4', '%s', '%s', '%s', '13');''' % (str(set[0]), str(group[0][0]), str(res[0][0])))